from decimal import Decimal
import re
import pymysql
import os,sys
import templates

# 插入mysql项目开发者活跃度比例（耗时长）

com_json=templates.DATA_JSON()
db_iotdb=templates.DB_IOTDB()
db_mysql=templates.DB_MYSQL()

class OPEN_MYSQL:
    def act_proportion(self):
        db_mysql.Connect()
        self.cursor = db_mysql.con.cursor()

        try:
            for i in range(100):
                table, goal = com_json.dir_json(i)
                sql = '''
                SELECT
                contributor,
                SUM(value_number) / (SELECT SUM(value_number) FROM users_month WHERE item = i.item) AS proportion
                FROM users_month i
                WHERE item='{}'
                GROUP BY contributor
                ORDER BY proportion DESC LIMIT 10;
                '''.format(table)
                # 执行sql命令
                self.cursor.execute(sql)
                # 获取所有查询结果
                data = self.cursor.fetchall()

                formatted_data = [
                    f"{{'contributor': '{entry[0]}', 'proportion': {float(entry[1])}}}"
                    for entry in data
                ]
                # 将所有元素连接为一个字符串，并去掉中括号
                result = ", ".join(formatted_data)
                results=str(result)
                insert_sql='''INSERT IGNORE INTO act_proportion (item,contribute_top10) VALUES ("{0}","{1}")'''.format(table,results)
                self.cursor.execute(insert_sql)
                db_mysql.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.cursor.close()
            db_mysql.con.close()

def decimal_default(obj):
    if isinstance(obj, Decimal):
        return float(obj)  # 将 Decimal 转换为 float

def inquire():
    select=OPEN_MYSQL()
    acts_proportion=select.act_proportion()
    return acts_proportion

data=inquire()
# print(data)
